package com.sihai.ehr.jdbc;

import org.apache.commons.lang.xwork.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * author: wxn
 * since: 13-8-7 下午23:22
 */
public class MemberService {
    private Connection connection;

    public void connection()throws Exception{
        connection = JDBCHelper.connection();
        if(connection == null){
            throw new IllegalArgumentException("jdbc连接失败");
        }
    }

    public void init(List<Member> members)throws Exception{
        Map<Integer, Department> map = new HashMap<Integer, Department>();

        if(members != null && members.size() > 0){
            PreparedStatement pst = connection.prepareStatement("insert into dt_user(user_serial, user_no, user_lname, dep_no, user_dep, user_depname, user_workday, user_duty, user_sex, user_nation, user_xueli, user_native, user_birthday, user_id, user_post, user_linkman, user_telephone, user_type, user_level, user_finger) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            for(int i=0; i<members.size(); i++){
                Member member = members.get(i);
                if(StringUtils.isBlank(member.getUser_no())){
                    System.out.println(String.format("员工初始化：插入第%s行数据(员工为%s)失败，失败原因：考勤工号为空", i+1, member.getUser_lname()));
                    continue;
                }
                Department department = null;
                int departmentId = member.getUser_dep();
                if(map.containsKey(departmentId)){ //部门ID
                    department = map.get(departmentId);
                }else{
                    department = findById(departmentId);
                    map.put(departmentId, department);
                }
                if(department == null){
                    System.out.println(String.format("员工初始化：插入第%s行数据(员工为%s)失败，失败原因：找不到部门", i+1, member.getUser_lname()));
                    continue;
                }

                pst.setInt(1, member.getUser_serial());
                pst.setString(2, member.getUser_no());
                pst.setString(3, member.getUser_lname());
                //pst.setString(4, member.getDep_no());
                pst.setString(4, department.getDep_no());
                pst.setInt(5, member.getUser_dep());
                //pst.setString(6, member.getUser_depname());
                pst.setString(6, department.getDep_name());
                pst.setString(7, member.getUser_workday());
                pst.setString(8, member.getUser_duty());
                pst.setString(9, member.getUser_sex());
                pst.setString(10, member.getUser_nation());
                pst.setString(11, member.getUser_xueli());
                pst.setString(12, member.getUser_native());
                pst.setString(13, member.getUser_birthday());
                pst.setString(14, member.getUser_id());
                pst.setString(15, member.getUser_post());
                pst.setString(16, member.getUser_linkman());
                pst.setString(17, member.getUser_telephone());
                pst.setInt(18, 0);
                pst.setInt(19, 0);
                pst.setString(20, "0000000000");
                int row = pst.executeUpdate();
                System.out.println(String.format("员工初始化：插入第%s行数据(员工为%s)%s", i+1, member.getUser_lname(), row > 0 ? "成功" : "失败"));
            }

            JDBCHelper.close(connection);
        }
    }

    public int insert(Member member)throws Exception{
        if(member != null){
            Department department = findById(member.getUser_dep());//得到部门
            PreparedStatement pst = connection.prepareStatement("insert into dt_user(user_serial, user_no, user_lname, dep_no, user_dep, user_depname, user_workday, user_duty, user_sex, user_nation, user_xueli, user_native, user_birthday, user_id, user_post, user_linkman, user_telephone, user_type, user_level, user_finger) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            pst.setInt(1, member.getUser_serial());
            pst.setString(2, member.getUser_no());
            pst.setString(3, member.getUser_lname());
            //pst.setString(4, member.getDep_no());
            pst.setString(4, department.getDep_no());
            pst.setInt(5, member.getUser_dep());
            //pst.setString(6, member.getUser_depname());
            pst.setString(6, department.getDep_name());
            pst.setString(7, member.getUser_workday());
            pst.setString(8, member.getUser_duty());
            pst.setString(9, member.getUser_sex());
            pst.setString(10, member.getUser_nation());
            pst.setString(11, member.getUser_xueli());
            pst.setString(12, member.getUser_native());
            pst.setString(13, member.getUser_birthday());
            pst.setString(14, member.getUser_id());
            pst.setString(15, member.getUser_post());
            pst.setString(16, member.getUser_linkman());
            pst.setString(17, member.getUser_telephone());
            pst.setInt(18, 0);
            pst.setInt(19, 0);
            pst.setString(20, "0000000000");
            int row = pst.executeUpdate();
            return row;
        }

        return 0;
    }

    public int update(Member member)throws Exception{
        if(member != null){
            //user_serial, user_no, user_lname, dep_no, user_dep, user_depname, user_workday, user_duty, user_sex, user_nation, user_xueli, user_native, user_birthday, user_id, user_post, user_linkman, user_telephone
            PreparedStatement pst = connection.prepareStatement("update dt_user set user_lname = ?, user_workday = ?, user_sex = ?, user_nation = ?, user_xueli = ?, user_native = ?, user_birthday = ?, user_id = ?, user_post = ?, user_telephone = ?");
            pst.setString(1, member.getUser_lname());
            pst.setString(2, member.getUser_workday());
            pst.setString(3, member.getUser_sex());
            pst.setString(4, member.getUser_nation());
            pst.setString(5, member.getUser_xueli());
            pst.setString(6, member.getUser_native());
            pst.setString(7, member.getUser_birthday());
            pst.setString(8, member.getUser_id());
            pst.setString(9, member.getUser_post());
            pst.setString(10, member.getUser_telephone());
            int row = pst.executeUpdate();
            return row;
        }

        return 0;
    }

    public int delete(int memberId)throws Exception{
        if(memberId > 0){
            PreparedStatement pst = connection.prepareStatement("delete from dt_user where user_serial = ?");
            pst.setInt(1, memberId);
            int row = pst.executeUpdate();
            return row;
        }

        return 0;
    }

    public int delete(String memberIds)throws Exception{
        if(StringUtils.isNotBlank(memberIds)){
            memberIds = memberIds.toLowerCase();
            if(StringUtils.contains(memberIds, "or") || StringUtils.contains(memberIds, "and")){
                return 0;
            }
            String sql = String.format("delete from dt_user where user_serial in (%s)", memberIds);
            PreparedStatement pst = connection.prepareStatement(sql);
            int row = pst.executeUpdate();
            return row;
        }

        return 0;
    }

    public Department findById(int id)throws Exception{
        Department department = new Department();
        try{
            PreparedStatement pst = connection.prepareStatement("select * from dt_dep where dep_serial = ?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                department.setDep_order(rs.getInt(1));
                department.setDep_parent(rs.getInt(2));
                department.setDep_order(rs.getInt(3));
                department.setDep_name(rs.getString(4));
                department.setDep_no(rs.getString(5));
                department.setDep_rule(rs.getString(6));
                break;
            }

        }catch(SQLException e) {
            e.printStackTrace();
        }

        return department;
    }
}
